﻿using System;
using System.Collections;
using System.Data;
using System.Linq;
using QuanLyTruongHoc_App.MyDataSet;
using QuanLyTruongHoc_App.MyDataSet.QLTHTableAdapters;
using System.Data.OleDb;

namespace QuanLyTruongHoc_App
{
    public static class PublicLib
    {
        public static String getStrConn()
        {
            return "Data Source=HOAND-VAIO\\SQLEXPRESS;Initial Catalog=QLTH;Persist Security Info=True;User ID=hoand;Password=hoa1985";
        }

        public static String getFullStrConn()
        {
            return "Provider=SQLOLEDB;" + getStrConn();
        }


        public static Boolean checkLogin(String _user, String _pass)
        {
            QLTH.tbl_UserDataTable tbl = new QLTH.tbl_UserDataTable();
            tbl_UserTableAdapter adap = new tbl_UserTableAdapter();
            adap.Connection.ConnectionString = PublicLib.getStrConn();
            adap.FillByUserPass(tbl, _user, _pass);
            
            return tbl.Rows.Count > 0;
        }

        public static string GetMD5Hash(string input)
        {
            System.Security.Cryptography.MD5CryptoServiceProvider x = new System.Security.Cryptography.MD5CryptoServiceProvider();
            byte[] bs = System.Text.Encoding.UTF8.GetBytes(input);
            bs = x.ComputeHash(bs);
            System.Text.StringBuilder s = new System.Text.StringBuilder();
            foreach (byte b in bs)
            {
                s.Append(b.ToString("x2").ToLower());
            }
            string password = s.ToString();
            return password;
        }

        public static String formatGVId(int id)
        {
            String result = "GV-" + id.ToString("00000");

            return result;
        }

        public static String formatHSId(int id)
        {
            String result = "HS-" + id.ToString("000000");

            return result;
        }

        public static int getMaxIdFromTbl(String _tblName, String _idCol)
        {
            String strSQL = "SELECT MAX([" + _idCol + "]) FROM [" + _tblName + "]";
            OleDbConnection conn = new OleDbConnection(getFullStrConn());
            conn.Open();
            OleDbCommand objCmd = new OleDbCommand(strSQL, conn);
            OleDbDataReader reader = objCmd.ExecuteReader();
            reader.Read();
            String str = reader.GetValue(0).ToString();
            conn.Close();
            if (str.ToUpper() == "")
                return 0;
            else
                return int.Parse(str);
        }

        public static Boolean updateField(String _tblName, String _keyCol, String _keyVal,
                                    String _col, String _newVal)
        {
            OleDbConnection conn = new OleDbConnection(getFullStrConn());
            conn.Open();
            try
            {
                String strSQL = "UPDATE [" + _tblName + "] SET [" + _col + "]=" + _newVal +
                                " WHERE [" + _keyCol + "]=" + _keyVal;
                OleDbCommand objCmd = new OleDbCommand(strSQL, conn);
                objCmd.ExecuteNonQuery();
                conn.Close();
                return true;
            }
            catch 
            {
                conn.Close();
                return false;
            }
        }

        public static String getOptionValue(String _key)
        {
            MyDataSet.QLTH.tbl_TuyChonDataTable tbl = new QLTH.tbl_TuyChonDataTable();
            MyDataSet.QLTHTableAdapters.tbl_TuyChonTableAdapter adap = new tbl_TuyChonTableAdapter();
            adap.Connection.ConnectionString = getStrConn();

            adap.Fill(tbl);
            DataRow []tmp = tbl.Select(tbl.idColumn.ColumnName + "='" + _key + "'");
            if (tmp.Length > 0)
                return tmp[0][tbl.valueColumn].ToString();

            return "";
        }

        public static void setOptionValue(String _key, String _value)
        {
            MyDataSet.QLTH.tbl_TuyChonDataTable tbl = new QLTH.tbl_TuyChonDataTable();
            MyDataSet.QLTHTableAdapters.tbl_TuyChonTableAdapter adap = new tbl_TuyChonTableAdapter();
            adap.Connection.ConnectionString = getStrConn();

            adap.Fill(tbl);
            DataRow[] tmp = tbl.Select(tbl.idColumn.ColumnName + "='" + _key + "'");
            if (tmp.Length > 0)
            {
                tmp[0][tbl.valueColumn] = _value;
                adap.Update(tmp[0]);
            }
            else
            {
                DataRow row = tbl.NewRow();
                row[tbl.idColumn] = _key;
                row[tbl.valueColumn] = _value;
                tbl.Rows.Add(row);
                adap.Update(row);
            }
        }
    }
}
